MySQL InnoDB의 MVCC가 읽기를 처리하는 방법

목차

• #문제가 되는 상황
• #MVCC가 해결하려는 문제
• #현재 행과 undo log로 이전 버전을 만든다
• #read view가 볼 수 있는 transaction을 판단한다
• #REPEATABLE READ와 READ COMMITTED의 snapshot 차이
• #자신이 변경한 행은 볼 수 있다
• #consistent read와 locking read의 차이
• #FOR UPDATE가 잠그는 범위는 index에 달려 있다
• #긴 transaction이 purge를 늦춘다
• #secondary index와 MVCC
• #운영에서 확인할 지표와 query
• #실전 점검 목록
• #결론
• #관련 노트

문제가 되는 상황

transaction A가 긴 보고서를 읽는 동안 transaction B가 주문 상태를 계속 수정한다고 하자. 모든 SELECT가 row lock을 잡으면 쓰기가 보고서 종료까지 기다려야 한다. 반대로 읽기가 항상 최신 물리 행만 보면 한 query 안에서도 시점이 뒤섞일 수 있다.

InnoDB의 MVCC는 변경된 행의 이전 상태를 undo 정보로 재구성하고 read view의 가시성 규칙에 맞는 버전을 선택한다. 덕분에 일반적인 consistent read와 write가 서로 덜 막힌다. 다만 SELECT ... FOR UPDATE 같은 locking read는 목적이 다르고, 오래 열린 snapshot은 이전 버전 정리를 지연시킨다.

MVCC가 해결하려는 문제

MVCC는 Multi-Version Concurrency Control의 약자다. 하나의 논리 행에 대해 transaction마다 볼 수 있는 버전을 선택해 읽기와 쓰기의 충돌을 줄인다.

flowchart LR
    C[현재 clustered row
status=paid] --> U1[undo
status=pending]
    U1 --> U2[older undo
status=created]
    R1[새 read view] --> C
    R2[오래된 read view] --> U1

transaction B가 pending → paid로 update해 commit한 뒤에도, 그 이전 snapshot을 가진 A는 undo를 따라 pending 버전을 볼 수 있다. A의 일반 SELECT가 B가 잡은 row lock을 기다리지 않고 자신의 snapshot을 읽을 수 있는 이유다.

MVCC는 “복사본 테이블을 transaction마다 만든다”는 뜻이 아니다. 현재 clustered index record와 undo log를 이용해 필요한 과거 버전을 재구성한다.

현재 행과 undo log로 이전 버전을 만든다

InnoDB는 내부적으로 row에 최근 변경 transaction ID와 undo record를 가리키는 roll pointer 같은 정보를 유지한다.

current row
  DB_TRX_ID  = transaction 120
  DB_ROLL_PTR → undo record for transaction 115

undo record 115
  previous values
  previous roll pointer → older version

UPDATE가 발생하면 이전 값을 복원하는 데 필요한 정보가 undo log에 기록된다. read view가 현재 version을 볼 수 없다고 판단하면 roll pointer를 따라 과거 version을 찾는다.

trx 120: status = paid
trx 115: status = pending
trx 101: status = created

rollback도 undo 정보를 사용하지만, consistent read를 위한 update undo는 관련 snapshot이 더 이상 필요 없을 때까지 보존되어야 한다. DELETE 역시 물리 row를 즉시 완전히 제거하는 대신 delete-mark와 undo를 거쳐 purge가 나중에 정리할 수 있다.

read view가 볼 수 있는 transaction을 판단한다

read view는 snapshot 생성 시점의 transaction 상태를 바탕으로 어떤 row version이 보이는지 판단한다. 구현 설명을 단순화하면 다음 정보가 중요하다.

• read view 생성 전에 commit된 transaction의 변경
• 생성 시점에 아직 active였던 transaction 목록
• 자신보다 이후에 시작한 transaction의 변경
• 현재 transaction 자신의 변경

Read View 생성 시점

commit 완료: 101, 102, 103
active:      104, 106
future:      107 이후

row의 DB_TRX_ID가 read view에서 보이지 않는 transaction에 속하면 undo chain의 이전 version을 확인한다. 이 과정을 “transaction ID 숫자가 작으면 무조건 보인다”는 단순 비교로 직접 구현하려 해서는 안 된다. active transaction 범위와 예외 규칙이 있다.

애플리케이션이 read view 내부 필드를 직접 다룰 일은 없지만, snapshot 시점과 statement 종류가 query 결과를 결정한다는 점을 이해하는 데 도움이 된다.

REPEATABLE READ와 READ COMMITTED의 snapshot 차이

InnoDB REPEATABLE READ에서는 한 transaction의 consistent read들이 첫 consistent read가 만든 snapshot을 공유한다.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT status FROM orders WHERE id = 42; -- snapshot 생성, pending

-- 다른 transaction이 status=paid로 commit

SELECT status FROM orders WHERE id = 42; -- 같은 snapshot, pending
COMMIT;

READ COMMITTED에서는 각 consistent read statement마다 새 snapshot을 사용한다.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT status FROM orders WHERE id = 42; -- pending

-- 다른 transaction이 status=paid로 commit

SELECT status FROM orders WHERE id = 42; -- 새 snapshot, paid 가능
COMMIT;

REPEATABLE READ에서도 transaction 시작 직후가 아니라 첫 consistent read 시점이 snapshot 기준이 될 수 있다. 정확한 시작 시점을 고정하려면 MySQL이 제공하는 consistent snapshot 시작 방식을 검토한다.

autocommit의 일반 SELECT는 statement 하나가 transaction이므로 다음 SELECT는 새 transaction·snapshot을 사용할 수 있다. “같은 connection에서 읽었으니 같은 snapshot”은 아니다.

자신이 변경한 행은 볼 수 있다

consistent read는 snapshot 이후 다른 transaction의 변경을 숨기지만 현재 transaction이 앞서 수행한 변경은 볼 수 있다.

START TRANSACTION;

SELECT status FROM orders WHERE id = 42; -- pending

UPDATE orders
SET status = 'cancelled'
WHERE id = 42;

SELECT status FROM orders WHERE id = 42; -- 자신의 cancelled 변경을 봄

ROLLBACK;

이 규칙 때문에 한 transaction에서 자신이 수정한 row는 최신 값, 수정하지 않은 row는 오래된 snapshot version을 조합해 실제 한 시점에 존재하지 않았던 table 상태처럼 보일 수 있다. 보고서 transaction 안에서 일부 row를 update하며 전체 snapshot을 해석할 때 주의한다.

MVCC snapshot은 DML statement의 search와 항상 동일하게 적용되는 것도 아니다. UPDATE·DELETE와 locking read는 최신 상태와 lock semantics를 사용하므로 일반 SELECT 결과와 다를 수 있다.

consistent read와 locking read의 차이

일반 SELECT는 READ COMMITTED와 REPEATABLE READ에서 보통 consistent nonlocking read다.

SELECT available_quantity
FROM inventory
WHERE product_id = 7;

읽은 뒤 해당 row를 변경할 계획이면 일반 SELECT만으로는 다른 transaction의 update/delete를 막지 않는다.

SELECT available_quantity
FROM inventory
WHERE product_id = 7
FOR UPDATE;

InnoDB locking read는 최신 사용 가능한 row를 대상으로 index record에 lock을 획득한다. 오래된 snapshot version을 lock할 수는 없다.

같은 transaction에서 다음 두 query가 다른 값을 반환할 수 있다.

SELECT status FROM orders WHERE id = 42;            -- snapshot value
SELECT status FROM orders WHERE id = 42 FOR UPDATE; -- latest value

이는 MVCC 오류가 아니라 읽기 목적이 다르기 때문이다.

FOR UPDATE가 잠그는 범위는 index에 달려 있다

InnoDB는 SQL의 추상 WHERE 문장을 기억해 잠그는 것이 아니라 query가 scan한 index record와 범위를 바탕으로 lock을 설정한다.

SELECT *
FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE;

status와 정렬을 지원하는 index가 없으면 많은 record를 scan하고 예상보다 넓게 lock할 수 있다. unique index의 정확한 equality lookup과 non-unique range scan은 record/gap lock 범위가 다르다.

CREATE INDEX idx_jobs_status_id
ON jobs (status, id);

queue worker는 SKIP LOCKED를 사용해 다른 worker가 잡은 job을 건너뛸 수 있다.

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

SKIP LOCKED 결과는 전체의 일관된 view가 아니므로 일반 업무 조회가 아니라 queue-like consumer에 제한한다. 누락 job을 다음 polling에서 다시 찾는 구조가 필요하다.

긴 transaction이 purge를 늦춘다

오래된 read view가 살아 있으면 InnoDB는 그 transaction이 볼 수 있는 과거 row version을 버릴 수 없다.

sequenceDiagram
    participant R as 긴 Report Transaction
    participant D as InnoDB
    participant W as Write Transactions
    R->>D: snapshot 생성
    loop 많은 update
      W->>D: 새 version + undo 생성
    end
    Note over D: R이 필요할 수 있어 오래된 undo purge 지연
    R->>D: COMMIT
    D->>D: 이후 purge 가능

긴 transaction의 영향은 다음과 같다.

• undo history 증가
• purge lag와 tablespace 사용 증가
• delete-marked record 정리 지연
• 과거 version 재구성 비용 증가
• schema 변경과 운영 작업 방해 가능

read-only transaction도 정기적으로 commit한다. 사용자가 dashboard를 열어 둔 시간 전체를 DB transaction으로 유지하거나 connection pool에 idle-in-transaction connection을 남기지 않는다.

큰 export는 작은 keyset page로 읽거나 replica·snapshot export 기능을 검토한다. 페이지마다 commit하면 한 시점 snapshot은 포기하게 되므로 제품 일관성 요구와 맞춰야 한다.

secondary index와 MVCC

InnoDB clustered index record에는 version 판단을 위한 내부 정보가 있지만 secondary index entry는 다르게 관리된다. secondary indexed column이 바뀌면 기존 entry가 delete-mark되고 새 entry가 추가될 수 있다.

consistent read가 secondary index를 통해 후보를 찾더라도 row version 확인을 위해 clustered record와 undo를 참고할 수 있다. 따라서 “covering secondary index면 모든 MVCC 읽기가 항상 clustered lookup 없이 끝난다”처럼 단정하지 않는다.

많이 변경되는 column에 secondary index가 많으면 각 update마다 index 유지·undo·purge 비용이 증가한다. 사용하지 않는 index를 제거하고 실행 계획과 write amplification을 측정한다.

운영에서 확인할 지표와 query

긴 transaction과 purge 지연을 찾기 위해 다음을 관찰한다.

• active transaction의 시작 시각과 경과 시간
• idle in transaction connection
• history list length·purge lag 관련 engine status
• undo tablespace 사용량
• oldest read view 또는 장기 consistent read
• lock wait와 deadlock
• update/delete rate와 purge 처리량

MySQL의 transaction metadata와 InnoDB status를 운영 권한 범위에서 조회한다.

SELECT
  trx_id,
  trx_state,
  trx_started,
  trx_mysql_thread_id,
  trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

query text에는 민감 정보가 들어갈 수 있으므로 monitoring 수집과 접근 권한을 제한한다. 장기 transaction을 자동 kill하기 전에 대상 업무와 rollback 비용을 확인하고 runbook을 따른다.

실전 점검 목록

consistent read는 read view를 기준으로 볼 수 있는 버전을 선택한다. UPDATE나 SELECT FOR UPDATE 같은 locking read는 최신 행을 대상으로 잠금을 획득한다.

결론

InnoDB MVCC는 현재 clustered row와 undo log로 과거 version을 재구성하고 read view에 보이는 version을 선택해 consistent read와 write의 충돌을 줄인다. REPEATABLE READ와 READ COMMITTED는 snapshot 생성 주기가 다르고, locking read는 snapshot이 아니라 최신 row와 index 범위를 잠근다. 긴 transaction은 undo purge를 지연시키므로 transaction 수명·지원 index·history와 lock 지표까지 운영에서 관찰해야 한다.

관련 노트

• 격리 수준과 Dirty Read Non-repeatable Read Phantom Read
• MySQL 복합 인덱스의 순서와 중복 인덱스 판단하기